package com.housekeeping.housekeeping.cleanerInformation.dao.impl;

import com.housekeeping.housekeeping.cleanerInformation.controller.CleanerImagesController;
import com.housekeeping.housekeeping.cleanerInformation.controller.CleanerUpdateImagesController;
import com.housekeeping.housekeeping.cleanerInformation.dao.ICleanerDao;
import com.housekeeping.housekeeping.cleanerInformation.entity.Cleaner;
import com.housekeeping.housekeeping.cleanerInformation.utils.DbManager;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

//jdbc:mysql://10.3.142.83:3306/housekeeping?serverTimezone=GMT%2B8

/**
 * @author hua
 * @version JDK1.8
 * @date 2021/1/18 22:52
 */
public class CleanerDaoImpl implements ICleanerDao {
    /**
     * 分页查询方法
     * @param limit 第几页
     * @param size 分页大小
     * @return 一页中的所有数据
     */
    @Override
    public List<Cleaner> selectByPage(int limit, int size) throws SQLException {
        //存放从数据库中查到的数据集合
        ArrayList<Cleaner> list = new ArrayList<>();
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;

        try {
            //获取连接对象
            connection = DbManager.getConnection();
            //mysql 语句
            String mysql = "SELECT * FROM cleaner_information WHERE is_delete = 1 LIMIT ?,?";
            //执行mysql语句
            ps = connection.prepareStatement(mysql);

            ps.setObject(1,limit);
            ps.setObject(2,size);
            //得到结果集
            resultSet = ps.executeQuery();
            //遍历结果集
            while (resultSet.next()){
                Cleaner cleaner = new Cleaner();
                cleaner.setId(resultSet.getInt("id"));
                cleaner.setPortrait(resultSet.getString("head_portrait"));
                cleaner.setName(resultSet.getString("name"));
                cleaner.setSex(resultSet.getString("sex"));
                cleaner.setAge(resultSet.getInt("age"));
                cleaner.setTelephoneNumber(resultSet.getString("telephone_number"));
                cleaner.setSynopsis(resultSet.getString("synopsis"));
                list.add(cleaner);
            }
        }  finally {
            //关流
            DbManager.closeAll(resultSet,connection,ps);
        }
        return list;
    }

    /**
     * 查询数据库数据的总条数
     * @return 数据库中数据的总条数
     */
    @Override
    public int selectCount() throws SQLException {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        int i = 0;
        try{
            connection = DbManager.getConnection();
            String mysql = "SELECT count(*) FROM cleaner_information WHERE is_delete = 1";
            ps = connection.prepareStatement(mysql);
            resultSet = ps.executeQuery();
            while (resultSet.next()){
                i = resultSet.getInt(1);
            }
        }finally {
            DbManager.closeAll(resultSet,connection,ps);
        }
        return i;
    }

    /**
     * 向数据库中插入保洁员的信息
     * @param cleaner 保洁员对象
     * @return 数据操作后数影响的行数
     */
    @Override
    public int insert(Cleaner cleaner) throws SQLException {
        //声明数据库连接对象
        Connection connection = null;
        //声明数据库连接流
        PreparedStatement ps = null;
        //数据库受影响行数
        int i = 0;

        try {
            //获取连接对象
            connection = DbManager.getConnection();
            //mysql 语句
            String mysql = "INSERT INTO cleaner_information(head_portrait, name, sex, age, telephone_number,synopsis) VALUES (?,?,?,?,?,?)";
            ps = connection.prepareStatement(mysql);
            //通过实体类对象获取要添加到数据库中的数据
            String portrait = CleanerImagesController.portrait;
            ps.setObject(1,portrait);
            ps.setObject(2,cleaner.getName());
            ps.setObject(3,cleaner.getSex());
            ps.setObject(4,cleaner.getAge());
            ps.setObject(5,cleaner.getTelephoneNumber());
            ps.setObject(6,cleaner.getSynopsis());

            CleanerImagesController.portrait = " ";
            //i 为数据库操作返回的受影响行数
            i = ps.executeUpdate();
        }finally {
            //关流
            DbManager.closeAll(connection,ps);
        }
        return i;
    }

    /**
     * 修改数据库中的保洁员信息
     * @param cleaner 保洁员对象
     * @return 数据库操作后受影响的行数
     */
    @Override
    public int update(Cleaner cleaner) throws SQLException {
        //声明数据库连接对象
        Connection connection = null;
        //声明数据库连接流
        PreparedStatement ps = null;
        //数据库操作受影响行数
        int i = 0;

        //如果修改保洁员头像接口中的保洁员头像地址不为空, 说明有修改保洁员头像
        if (CleanerUpdateImagesController.portrait != ""){
            try{
                connection = DbManager.getConnection();
                String mysql = "UPDATE cleaner_information SET head_portrait = ?, name = ?, sex = ?, age = ?, telephone_number = ?, synopsis = ? WHERE id = ? ";
                ps = connection.prepareStatement(mysql);
                //修改保洁员头像的地址
                ps.setObject(1,CleanerUpdateImagesController.portrait);
                //修改完后将, 修改保洁员头像的接口中的保洁员头像地址清空,
                // 防止下一次修改保洁员信息且没有修改保洁员头像时, 修改保洁员头像接口中的保洁员头像地址还存在
                CleanerUpdateImagesController.portrait = "";

                ps.setObject(2,cleaner.getName());
                ps.setObject(3,cleaner.getSex());
                ps.setObject(4,cleaner.getAge());
                ps.setObject(5,cleaner.getTelephoneNumber());
                ps.setObject(6,cleaner.getSynopsis());
                ps.setObject(7,cleaner.getId());
                i = ps.executeUpdate();
            }finally {
                DbManager.closeAll(connection,ps);
            }
        }else{
            //如果修改保洁员头像的接口中的保洁员头像地址为空, 说明保洁员头像没有修改
            try {
                connection = DbManager.getConnection();
                String mysql = "UPDATE cleaner_information SET name = ?, sex = ?, age = ?, telephone_number = ?, synopsis = ? WHERE id = ? ";
                ps = connection.prepareStatement(mysql);
                ps.setObject(1,cleaner.getName());
                ps.setObject(2,cleaner.getSex());
                ps.setObject(3,cleaner.getAge());
                ps.setObject(4,cleaner.getTelephoneNumber());
                ps.setObject(5,cleaner.getSynopsis());
                ps.setObject(6,cleaner.getId());
                i = ps.executeUpdate();
            }finally {
                DbManager.closeAll(connection,ps);
            }
        }
        return i;
    }

    /**
     * 删除某个保洁员 根据id删除
     * @param id
     * @return
     */
    @Override
    public int delete(int id) throws SQLException {
        //声明数据库连接对象
        Connection connection = null;
        //声明数据库连接流
        PreparedStatement ps = null;
        //数据库操作后返回的受影响行数
        int i = 0;

        try {
            connection = DbManager.getConnection();
            String mysql = "UPDATE cleaner_information SET is_delete = 0 WHERE id = ?";
            ps = connection.prepareStatement(mysql);
            ps.setObject(1,(int)id);
            i = ps.executeUpdate();
        }finally {
            //关流
            DbManager.closeAll(connection,ps);
        }

        return i;
    }

    /**
     * 根据 id 批量删除保洁员
     * @param list 存放id的集合
     * @return
     * @throws SQLException
     */
    @Override
    public int deleteAll(List<Cleaner> list) throws SQLException {
        //声明数据库连对象
        Connection connection = null;
        //声明数据库连接流
        PreparedStatement ps = null;
        //数据库操作受影响行数
        int i = 0;
        try {
            //获取数据库连接对象
            connection = DbManager.getConnection();
            //mysql语句
            String mysql = "UPDATE cleaner_information SET is_delete = 0 WHERE id = ?";
            ps = connection.prepareStatement(mysql);
            for (Cleaner c:list) {
                ps.setObject(1,c.getId());
                i = ps.executeUpdate();
            }

        }finally {
            DbManager.closeAll(connection,ps);
        }

        return i;
    }

    /**
     * 模糊查询功能, 根据名字查询
     * @param limit
     * @param size
     * @return
     */
    @Override
    public List<Cleaner> vagueSelectName(int limit, int size, String name) throws SQLException {
        ArrayList<Cleaner> list = new ArrayList<>();
        //声明数据库连接对象
        Connection connection = null;
        //声明数据库连接流
        PreparedStatement ps = null;
        //声明结果集
        ResultSet resultSet = null;

        try {
            //获取数据库连接对象
            connection = DbManager.getConnection();
            //mysql语句
            String mysql = "select *from cleaner_information where is_delete = 1 AND name like "+"'"+"%" + name + "%"+"' LIMIT ?,?";
            //执行mysql语句
            ps = connection.prepareStatement(mysql);

            ps.setObject(1,limit);
            ps.setObject(2,size);
            //获取查询结果集
            resultSet = ps.executeQuery();
            //遍历结果集
            while (resultSet.next()){
                Cleaner cleaner = new Cleaner();
                cleaner.setId(resultSet.getInt("id"));
                cleaner.setPortrait(resultSet.getString("head_portrait"));
                cleaner.setName(resultSet.getString("name"));
                cleaner.setSex(resultSet.getString("sex"));
                cleaner.setAge(resultSet.getInt("age"));
                cleaner.setTelephoneNumber(resultSet.getString("telephone_number"));
                cleaner.setSynopsis(resultSet.getString("synopsis"));
                list.add(cleaner);
            }
        }finally {
            //关流
            DbManager.closeAll(resultSet,connection,ps);
        }
        return list;
    }

    /**
     * 精确查询, 根据号码精确查询
     * @param limit
     * @param size
     * @param telephoneNumber
     * @return
     */
    @Override
    public List<Cleaner> selectTelephoneNumber(int limit, int size, String telephoneNumber) throws SQLException {
        ArrayList<Cleaner> list = new ArrayList<>();
        //声明数据库连接对象
        Connection connection = null;
        //声明数据库连接流
        PreparedStatement ps = null;
        //声明结果集
        ResultSet resultSet = null;

        try {
            //获取数据库连接对象
            connection = DbManager.getConnection();
            //mysql语句
            String mysql = "select *from cleaner_information where is_delete = 1 AND telephone_number = ? LIMIT ?,?";
            //执行mysql语句
            ps = connection.prepareStatement(mysql);

            ps.setObject(1,telephoneNumber);
            ps.setObject(2,limit);
            ps.setObject(3,size);
            //获取查询结果集
            resultSet = ps.executeQuery();
            //遍历结果集
            while (resultSet.next()){
                Cleaner cleaner = new Cleaner();
                cleaner.setId(resultSet.getInt("id"));
                cleaner.setPortrait(resultSet.getString("head_portrait"));
                cleaner.setName(resultSet.getString("name"));
                cleaner.setSex(resultSet.getString("sex"));
                cleaner.setAge(resultSet.getInt("age"));
                cleaner.setTelephoneNumber(resultSet.getString("telephone_number"));
                cleaner.setSynopsis(resultSet.getString("synopsis"));
                list.add(cleaner);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //关流
            DbManager.closeAll(resultSet,connection,ps);
        }
        return list;
    }
}
